using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Xml;
using System.Threading;
using System.IO.Packaging;
using System.Data.Common;

using LogAnalyzerIIS.Objects;
using LogAnalyzerIIS.Helpers;

namespace LogAnalyzerIIS.Exporters
{
	public partial class ExportToSQLWizard : Form
	{
		public ExportToSQLWizard()
		{
			InitializeComponent();


			if (wizardControl.SelectedIndex == 0)
				btnBack.Enabled = false;
		}

		public ExportToSQLWizard(string fileName)
		{
			InitializeComponent();
			xlsxFileName = fileName;

			if (wizardControl.SelectedIndex == 0)
				btnBack.Enabled = false;
		}

		private void btnNext_Click(object sender, EventArgs e)
		{
			wizardControl.SelectedIndex++;

			if (wizardControl.SelectedIndex == wizardControl.TabPages.Count - 1)
				btnNext.Enabled = false;
		}

		private void btnCancel_Click(object sender, EventArgs e)
		{
			try
			{
				System.Delegate del = new ThreadingHelper.VoidDelegate(FinalizeThread);
				this.BeginInvoke(del);
			}
			finally
			{
				CloseLogFile();
				CloseDB();
			}
			this.Close();
		}

		private void btnBack_Click(object sender, EventArgs e)
		{
			wizardControl.SelectedIndex--;

			if (wizardControl.SelectedIndex == 0)
				btnBack.Enabled = false;
		}


		/***********************************/
		/* Page switching wizard functions */
		/***********************************/

		private void wizardControl_Selecting(object sender, TabControlCancelEventArgs e)
		{
			// THIS EVENT ALLOWS YOU TO CANCEL THE TAB PAGE OR CHANGE THE DESTINATION PAGE
			// if the wrong page is selected then e.cancel == true;
			int OldIndex = (int)wizardControl.Tag;
			switch (OldIndex)
			{
				// Provider page
				case pageProvider:
					// Get next page index
					int newIndex = GetProviderPage();
					e.Cancel = true;

					// New page index
					switch (newIndex)
					{
						// ODBC data provider
						case pageConnectionOdbc:
							// Prepare ODBC data provider page for switching
							if (!PrepareOdbc())
								return;
							break;

						// OLE DB data provider
						case pageConnectionOleDb:
							// Prepare OLE DB data provider page for switching
							if (!PrepareOleDb())
								return;
							break;

						// Prepare SQLClient data provider page for switching
						case pageConnectionSQLServ:
							if (!PrepareSQLServ())
								return;
							break;
					}

					wizardControl.SelectedIndex = newIndex;
					// Allow page switching
					e.Cancel = false;
					break;

				// ODBC data provider page
				case pageConnectionOdbc:
					// If Back button is pressed go to the first page
					if (e.TabPageIndex < (int)wizardControl.Tag)
						wizardControl.SelectedIndex = pageProvider;
					else
					{
						// If Next button is pressed then go to tables page
						e.Cancel = true;
						if (oDBCProviderHasChanged)
							if (!TryConnectToDB(GetOdbcConnectionString()))
								break;
						e.Cancel = false;
						wizardControl.SelectedIndex = pageTable;
					}
					break;
				// SQLClient data provider
				case pageConnectionSQLServ:
					if (e.TabPageIndex < (int)wizardControl.Tag)	// If Back button is pressed
						wizardControl.SelectedIndex = pageProvider;	// go to the first page
					else // If Next button is pressed
					{	// then go to tables page
						//// Disallow page switching if something is wrong
						e.Cancel = true;
						if (ProviderChanged_SQLClient)
							if (!TryConnectToDB(GetSQLClientConnectionString()))
								break;
						e.Cancel = false;
						wizardControl.SelectedIndex = pageTable;
					}
					break;

				// OLE DB data provider
				case pageConnectionOleDb:
					// If Back button is pressed, go to the first page
					if (e.TabPageIndex < (int)wizardControl.Tag)
						wizardControl.SelectedIndex = pageProvider;
					else
					{
						// If Next button is pressed,  go to tables page

						e.Cancel = true;
						if (providerChangedOLEDB)
							if (!TryConnectToDB(GetOleDbConnectionString()))
								break;
						e.Cancel = false;
						wizardControl.SelectedIndex = pageTable;
					}
					break;
				// Unsupported data provider
				case pageConnectionOther:
					// If Back button is pressed go to the first page
					if (e.TabPageIndex < (int)wizardControl.Tag)
						wizardControl.SelectedIndex = pageProvider;
					break;

				// "Tables" page
				case pageTable:
					// If Back button is pressed, go to the provider page
					if (e.TabPageIndex < (int)wizardControl.Tag)
						wizardControl.SelectedIndex = GetProviderPage();
					else
					{
						// if there isn't any field changed on ODBC data provider
						if (!TableNameChanged) // then don't reload column names
							break;

						// Get table name
						TableName = lbTableNames.SelectedItem.ToString();

						if (LoadDBColNames())	// Load database column names
						{
							MatchColumnNames();	// Make column matching
							break;
						}

						// Disallow page switching if something is wrong
						e.Cancel = true;
					}
					break;
				// Column matching page
				case pageMatching:
					if (e.TabPageIndex < (int)wizardControl.Tag)
						wizardControl.SelectedIndex = pageTable;
					else
						// cancel if validating cells goes wrong
						if (!ValidateGridCells())
							e.Cancel = true;
						else
							if (!AlterTable())
								e.Cancel = true;
					break;

				// Progress page
				case pageProgress:
					break;
			}
		}

		private void wizardControl_SelectedIndexChanged(object sender, EventArgs e)
		{
			wizardControl.Tag = wizardControl.SelectedIndex;
			// this is liek the after switch pages event
			switch (wizardControl.SelectedIndex)
			{
				case pageProvider:
					cbProvider_SelectedIndexChanged(null, null);
					break;

				// OLE DB data provider page
				case pageConnectionOleDb:
					TryOleDbNextButtonEnable();

					// Set "changes" flag
					providerChangedOLEDB = false;
					btnBack.Enabled = true;
					break;

				// ODBC data provider page
				case pageConnectionOdbc:
					TryOdbcNextButtonEnable();

					oDBCProviderHasChanged = false;
					btnBack.Enabled = true;
					break;

				// SQLClient data provider page
				case pageConnectionSQLServ:
					ProviderChanged_SQLClient = false;

					TrySQLClientNextButtonEnable();
					btnBack.Enabled = true;
					break;

				// Unsupported data provider page
				case pageConnectionOther:
					btnNext.Enabled = false;
					btnBack.Enabled = true;
					break;

				// Tables page
				case pageTable:
					TryTablesNextButtonEnable();

					TableNameChanged = false;
					btnBack.Enabled = true;
					break;

				// Column matching page
				case pageMatching:
					// if there is any matched columns enable the next button
					if (dgMatches.Rows.Count > 0)
						btnNext.Enabled = true;
					else
						btnNext.Enabled = false;
					lbLogColumns_SelectedIndexChanged(sender, null);
					btnBack.Enabled = true;
					break;

				// Progress page
				case pageProgress:
					wizardControl.Invalidate();
					wizardControl.Update();

					pbConverting.Maximum = sheetData.ChildNodes.Count - 1;
					pbConverting.Step = 1;
					pbConverting.Value = 0;

					btnNext.Enabled = false;
					btnBack.Enabled = false;
					lbProgressLog.Items.Clear();
					lbProgressLog.Items.Add(ThreadingHelper.FormatLogRow("Export Started"));
					thread = new Thread(new ThreadStart(ThreadStart));
					thread.Priority = ThreadPriority.AboveNormal;
					thread.Start();

					break;
			}
		}

		#region Xlsx-file vars
		/*****************************************/
		/* Variables for working with xlsx-files */
		/*****************************************/

		// Xlsx-file name
		public string xlsxFileName = "";

		// Shema names for xlsx-file
		private const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";
		private const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
		private const string sharedStringsRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings";
		private const string sharedStringSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";

		private Package xlsxFile = null;	// System.IO.Packaging.Package - xlsx(zip)-file
		private XmlNamespaceManager nm;		// Xml namespace manger for searching data in xlsx-file
		private XmlDocument sheet;			// Xml document for working with sheet data in xlsx-file
		private XmlNode sheetData;			// Starting point of cell data in sheet data
		private XmlDocument strs;			// Xlm document for working with shared strings in xlsx-file

		private List<ExcelCell> lbocXlsxRow = null;	// List of xlsx-file columns

		#endregion

		#region DB vars
		/****************************************/
		/* Variables for working with databases */
		/****************************************/

		private System.Data.DataTable dtProviders;	// List of data providers

		DbProviderFactory Factory = null;	        // Data provider factory
		private DbConnection Conn = null;			// Database connection
		private DbCommand Cmd = null;				// Database command
		private DbDataAdapter Adapter = null;		// Database data adapter
		private System.Data.DataSet dBColumnSet = null;	// Dataset object

		private List<DBColumn> lbocDBRow = null;		// List of database columns

		private List<string> dbTableNames = null;		// List of database tables
		private string TableName;						// Database table name
		private bool TableNameChanged = true;			// Table selection page
		private List<DBColumn> dbNewColumns = null;		// New columns to be created in database

		#endregion

		#region Numbers of wizard's pages

		private const int pageProvider = 0;
		private const int pageConnectionOdbc = 1 + pageProvider;
		private const int pageConnectionOleDb = 1 + pageConnectionOdbc;
		private const int pageConnectionSQLServ = 1 + pageConnectionOleDb;
		private const int pageConnectionOther = 1 + pageConnectionSQLServ;
		private const int pageTable = 1 + pageConnectionOther;
		private const int pageMatching = 1 + pageTable;
		private const int pageProgress = 1 + pageMatching;

		#endregion

		#region Xlsx functions
		/*****************************************/
		/* Functions for working with xlsx-files */
		/*****************************************/

		/// <summary>
		/// Tries to open specified xlsx-file and returns true if success
		/// </summary>
		/// <param name="xlsxFileName">The name of xlsx-file to open</param>
		/// <returns>True if success</returns>
		private bool TryOpenLogFile(string xlsxFileName)
		{
			try
			{
				// Try to open xlsx-file (zip package)
				xlsxFile = ZipPackage.Open(xlsxFileName, FileMode.Open, FileAccess.Read);
			}
			catch
			{
				MessageBox.Show("File " + xlsxFileName + " not found.", "Open error", MessageBoxButtons.OK, MessageBoxIcon.Error);

				return false;
			}

			// URI of document
			Uri documentURI = null;

			// Document part in package
			PackagePart documentPart = null;

			// Get document part in package
			foreach (PackageRelationship xlsxRelationship in xlsxFile.GetRelationshipsByType(documentRelationshipType))
			{
				// Create document part URI
				documentURI = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative), xlsxRelationship.TargetUri);
				documentPart = xlsxFile.GetPart(documentURI);

				break;
			}

			bool result = false;

			if (documentPart != null)
			{
				// Load document from package
				XmlDocument doc = new XmlDocument();
				doc.Load(documentPart.GetStream());

				// Create namespace manager for searching and add a "d" namespace
				NameTable nt = new NameTable();
				nm = new XmlNamespaceManager(nt);
				nm.AddNamespace("d", worksheetSchema);

				// Search for "sheets" element in document 
				XmlNode shn = doc.SelectSingleNode("//d:sheets", nm);

				if (shn != null)
				{
					// check if there is first sheet info exists
					if (shn.FirstChild != null)
					{
						// Get relationship for first sheet
						PackageRelationship sheetRel = documentPart.GetRelationship(shn.FirstChild.Attributes["r:id"].Value);

						// Find sheet part in package
						Uri sheetUri = PackUriHelper.ResolvePartUri(documentURI, sheetRel.TargetUri);
						PackagePart sheetPart = xlsxFile.GetPart(sheetUri);

						if (sheetPart != null)
						{
							// Load sheet part from package
							sheet = new XmlDocument();
							sheet.Load(sheetPart.GetStream());

							// Search for starting point of sheet data
							sheetData = sheet.SelectSingleNode("//d:sheetData", nm);

							if (sheetData != null)
							{
								// Load shared strings from package
								strs = null;
								foreach (PackageRelationship strsRel in documentPart.GetRelationshipsByType(sharedStringsRelationshipType))
								{
									if (strsRel != null)
									{
										// Get URI of shared strings part
										Uri strsUri = PackUriHelper.ResolvePartUri(documentURI, strsRel.TargetUri);
										PackagePart strsPart = xlsxFile.GetPart(strsUri);

										if (strsPart != null)
										{
											// Load shared strings part
											strs = new XmlDocument();
											strs.Load(strsPart.GetStream());

											// Add shared strings "s" namespace to namespace manager
											nm.AddNamespace("s", sharedStringSchema);
											break;
										}
									}
								}

								result = true;
							}
							else
								MessageBox.Show("Cannot find cell data in the sheet in " + xlsxFileName,
								"Xlsx error", MessageBoxButtons.OK, MessageBoxIcon.Error);
						}
						else
							MessageBox.Show("Cannot find sheet in " + xlsxFileName,
									"Xlsx error", MessageBoxButtons.OK, MessageBoxIcon.Error);
					}
					else
						MessageBox.Show("There is no any sheet in " + xlsxFileName,
								"Xlsx error", MessageBoxButtons.OK, MessageBoxIcon.Error);
				}
				else
					MessageBox.Show("Cannot find sheet collection in " + xlsxFileName,
							"Xlsx error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
			else
				MessageBox.Show("Cannot find document part in " + xlsxFileName,
						"Xlsx error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			return result;
		}

		/// <summary>
		/// Close xlsx-file
		/// </summary>
		private void CloseLogFile()
		{
			if (xlsxFile != null)
			{
				xlsxFile.Close();
				xlsxFile = null;
			}
		}

		/// <summary>
		/// Returns type of currently processed xlsx-cell
		/// </summary>
		/// <param name="col">XmlNode of currentry processed cell</param>
		/// <returns>Type of currently processed cell</returns>
		private string GetXlsxCellType(XmlNode col)
		{
			string cellType = string.Empty;

			// Get cell's type attribute
			XmlAttribute cellAttr = col.Attributes["t"];

			// Get the type if attribute exists
			if (cellAttr != null)
				cellType = cellAttr.InnerText;

			return cellType;
		}

		/// <summary>
		/// Returns value of currently processes xlsx-cell
		/// </summary>
		/// <param name="col">XmlNode of currentry processed cell</param>
		/// <param name="strs">XmlNode with shared strings data</param>
		/// <param name="nm">Namespace manager</param>
		/// <returns>Value of currently processed cell</returns>
		private string GetXlsxCellValue(XmlNode col, XmlNode strs, XmlNamespaceManager nm)
		{
			return GetXlsxCellValue(col, GetXlsxCellType(col), strs, nm);
		}

		/// <summary>
		/// Returns value of currently processes xlsx-cell
		/// </summary>
		/// <param name="col">XmlNode of currentry processed cell</param>
		/// <param name="cellType">Type of currentry processed cell</param>
		/// <param name="strs">XmlNode with shared strings data</param>
		/// <param name="nm">Namespace manager</param>
		/// <returns>Value of currently processed cell</returns>
		private string GetXlsxCellValue(XmlNode col, string cellType, XmlNode strs, XmlNamespaceManager nm)
		{
			string cellValue = string.Empty;
			XmlNode cellNode = col.SelectSingleNode("d:v", nm);

			if (cellNode != null)
				cellValue = cellNode.InnerText;

			if (cellType == "s")
			{
				string strIndex = string.Format("//s:sst/s:si[{0}]", Convert.ToInt32(cellValue) + 1);

				XmlNode strNode = strs.SelectSingleNode(strIndex, nm);

				if (strNode != null)
					cellValue = strNode.InnerText;
			}

			return cellValue;
		}

		/// <summary>
		/// Loads column names from xlsx-file
		/// </summary>
		/// <returns>True if success</returns>
		private bool LoadExcelColumnNames()
		{
			if (xlsxFile == null)
				return false;

			// Create new list for storing names
			lbocXlsxRow = new List<ExcelCell>();

			// Load column names
			foreach (XmlNode row in sheetData.ChildNodes)
			{
				foreach (XmlNode col in row.ChildNodes)
				{
					// Get cell value
					string cellValue = GetXlsxCellValue(col, strs, nm);

					// Add cell to list
					lbocXlsxRow.Add(new ExcelCell(cellValue, col.Attributes["r"].Value));
				}

				break;	// After processing first row
			}
			return true;
		}

		#endregion

		#region DB functions
		/***************************************/
		/* Functions for working with databases*/
		/***************************************/

		/// <summary>
		/// Returns the number of the data provider page based on selected index in provider list
		/// </summary>
		/// <returns>Number of data provider page</returns>
		private int GetProviderPage()
		{
			string s = dtProviders.Rows[cbProvider.SelectedIndex].ItemArray[2].ToString();

			// Return ODBC data provider page number
			if (s == "System.Data.Odbc")
				return pageConnectionOdbc;

			// Return OLE DB data provider page number
			if (s == "System.Data.OleDb")
				return pageConnectionOleDb;

			// Return SQLClient data provider page number
			if (s == "System.Data.SqlClient")
				return pageConnectionSQLServ;

			// Return Unsupported data provider page number
			return pageConnectionOther;
		}

		/// <summary>
		/// Tries to connect to a database with conxString connection string
		/// </summary>
		/// <param name="conxString">Connection string value</param>
		/// <returns>True if success</returns>
		private bool TryConnectToDB(string connectionString)
		{
			// Get data provicer factory
			Factory = DbProviderFactories.GetFactory(dtProviders.Rows[cbProvider.SelectedIndex][2].ToString());
			Conn = null;
			Cmd = null;
			Adapter = null;

			if (Factory != null)
			{
				// Create connection objects
				Conn = Factory.CreateConnection();

				// Set the connection string
				Conn.ConnectionString = connectionString;

				try
				{
					// Try to open connection to database
					Conn.Open();

					// Create command object
					Cmd = Factory.CreateCommand();
					Cmd.Connection = Conn;

					// Create data adapter object
					Adapter = Factory.CreateDataAdapter();

					// Create dataset object
					dBColumnSet = new DataSet();

					// load the table names into the database
					LoadDBTableNames();

					return true;
				}
				catch (Exception Exc)
				{
					// Show message if error
					MessageBox.Show(Exc.Message, Exc.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);

					return false;
				}
			}
			else
				return false;
		}

		/// <summary>
		/// Close database connection
		/// </summary>
		private void CloseDB()
		{
			if (Conn != null && Conn.State != ConnectionState.Closed)
				Conn.Close();
		}

		/// <summary>
		/// Create table with specified name in database
		/// </summary>
		/// <param name="tblName">Table name</param>
		/// <returns>True if success</returns>
		private bool CreateNewTable(string tblName)
		{
			if (Conn == null)
				return false;

			Cmd.CommandText =
					"create table " + tblName + "\r\n" +
					"(\r\n" +
					"[Date] datetime null,\r\n" +
					"[Time] datetime null,\r\n" +
					"[Client IP] varchar(255),\r\n" +
					"[User Name] varchar(96),\r\n" +
					"[Service Name] varchar(255),\r\n" +
					"[Server Name] varchar(255),\r\n" +
					"[Server IP] varchar(255),\r\n" +
					"[Server Port] int,\r\n" +
					"[Method] varchar(8),\r\n" +
					"[URI Stem] varchar(255),\r\n" +
					"[URI Query] varchar(255),\r\n" +
					"[Protocol Status] int,\r\n" +
					"[Win32 Status] varchar(16),\r\n" +
					"[Bytes Sent] int,\r\n" +
					"[Bytes Retrieved] int,\r\n" +
					"[Time Taken] varchar(16),\r\n" +
					"[Protocol Version] varchar(100),\r\n" +
					"[Host] char(255),\r\n" +
					"[User Agent] varchar(255),\r\n" +
					"[Cookie] varchar(255),\r\n" +
					"[Referer] varchar(255),\r\n" +
					"[Bytes Transmitted] int,\r\n" +
					"[UNKNOWN DATA] varchar(255)\r\n)\r\n";

			try
			{	// Try to run the command
				Cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);

				return false;
			}

			return true;
		}

		/// <summary>
		/// Gets all names of tables in database
		/// </summary>
		/// <returns>True if success</returns>
		private bool LoadDBTableNames()
		{
			// Create new list for storing names
			dbTableNames = new List<string>();
			lbTableNames.Items.Clear();

			// Get tables
			DataTable tableSchema = Conn.GetSchema("Tables");

			if (tableSchema == null)
				return false;

			// Add tables to the tables list
			foreach (DataRow dr in tableSchema.Rows)
			{
				dbTableNames.Add(dr.ItemArray[2].ToString());
				lbTableNames.Items.Add(dr.ItemArray[2].ToString());
			}

			return true;
		}

		/// <summary>
		/// Validate fields on "Tables" page and enable Next button if it's all Ok
		/// </summary>
		private void TryTablesNextButtonEnable()
		{
			if (lbTableNames.SelectedIndex >= 0)	// If table is selected then
				btnNext.Enabled = true;		// enable Next button
			else
				btnNext.Enabled = false;	// else disable it
		}

		/// <summary>
		/// Fills a listbox with table names
		/// </summary>
		private void FillTableNamesList()
		{
			if (dbTableNames == null)
				return;

			lbTableNames.Items.Clear();

			if (dbTableNames.Count <= 0)
				return;

			foreach (string s in dbTableNames)
				lbTableNames.Items.Add(s);
		}

		/// <summary>
		/// Gets column names from database table
		/// </summary>
		/// <returns>True if success</returns>
		private bool LoadDBColNames()
		{
			if (Conn == null)
				return false;

			Cmd.Connection = Conn;
			Cmd.CommandText = "SELECT * FROM " + TableName;

			Adapter.SelectCommand = Cmd;

			try
			{
				// Try to get data from table
				Adapter.Fill(dBColumnSet);
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);

				return false;
			}


			// Create new list for storing database table column names
			lbocDBRow = new List<DBColumn>();

			foreach (DataColumn dataColumn in dBColumnSet.Tables[0].Columns)
				lbocDBRow.Add(new DBColumn(dataColumn.ColumnName, dataColumn.DataType.Name, dataColumn.MaxLength));

			// Return success
			return true;
		}

		#endregion

		/// <summary>
		/// Makes automatic column matching based on their names
		/// </summary>
		private void MatchColumnNames()
		{
			// Clear listboxes
			lbLogColumns.Items.Clear();
			lbDBColumns.Items.Clear();
			dgMatches.Rows.Clear();

			// Create new temporary list for matched cols
			List<DBColumn> dBColumnMatches = new List<DBColumn>();

			// Matching cycle
			foreach (ExcelCell logColumn in lbocXlsxRow)
			{
				// Flag determined that current xlsx-name is not matched
				bool logColumnNotMatched = true;

				foreach (DBColumn dBColumn in lbocDBRow)
				{
					// If database table column is contained in matched list then skip it
					if (dBColumnMatches.Contains(dBColumn))
						continue;

					// Try to find - we try to match
					if (logColumn.ToString() == dBColumn.ToString())
					{
						object[] matchRow = new object[4];
						matchRow[0] = logColumn;
						matchRow[1] = dBColumn;
						dgMatches.Rows.Add(matchRow);

						dBColumnMatches.Add(dBColumn);
						logColumnNotMatched = false;

						break;
					}
				}

				// add it to the leftover list if it hasnt been matched
				if (logColumnNotMatched)
					lbLogColumns.Items.Add(logColumn);
			}

			// add any missed out columns to the leftover table list
			foreach (DBColumn dbStr in lbocDBRow)
			{
				if (!dBColumnMatches.Contains(dbStr))
					lbDBColumns.Items.Add(dbStr);
			}
		}

		/// <summary>
		/// Validates rows in a center list on a matching page in the wizard
		/// </summary>
		/// <returns>True if success</returns>
		public bool ValidateGridCells()
		{
			int rowNumber = 0;
			int rowCount = dgMatches.Rows.Count;

			while (rowNumber < rowCount)
			{
				// If any cell (r or l) is empty then show error
				if ((dgMatches.Rows[rowNumber].Cells[0].Value.ToString() == "") ||
						(dgMatches.Rows[rowNumber].Cells[1].Value.ToString() == ""))
				{
					MessageBox.Show("Some row(-s) is/are empty.", "Validating match", MessageBoxButtons.OK, MessageBoxIcon.Error);

					return false;
				}

				++rowNumber;
			}

			return true;
		}

		/// <summary>
		/// Converts data from xlsx to database
		/// </summary>
		/// <returns>True if success</returns>
		private bool ConvertData()
		{
			// If there is no any matched columns - return failure
			if (Conn == null || dgMatches.Rows.Count <= 0)
				return false;

			Delegate delegatePerformStep = new ThreadingHelper.VoidDelegate(PerformStep);
			Delegate delegateAddItemToListBox = new ThreadingHelper.ListBoxDelegate(AddItemToListBox);

			// Make insert quary
			string queryInsertColumns = "INSERT INTO " + TableName + " (";

			foreach (DataGridViewRow matchRow in dgMatches.Rows)
				queryInsertColumns += "[" + ((DBColumn)matchRow.Cells[1].Value).ColumnName + "],";

			queryInsertColumns = queryInsertColumns.Substring(0, queryInsertColumns.Length - 1);
			queryInsertColumns += ") VALUES (";

			// Move to the data cells (skip header)
			XmlNode xlsxRow = sheetData.FirstChild;
			xlsxRow = xlsxRow.NextSibling;

			// number of row, used for exporting log output
			int RowNum = 0;

			// Process each row in sheet
			while (xlsxRow != null)
			{
				// process each cell in row
				foreach (XmlNode xlsxCol in xlsxRow.ChildNodes)
				{
					// Get position of currently processed cell
					string columnPosition = ExcelHelper.GetColumnPosition(xlsxCol.Attributes["r"].Value);

					// Get cell type and value
					string cellType = GetXlsxCellType(xlsxCol);
					string cellValue = GetXlsxCellValue(xlsxCol, cellType, strs, nm);

					// Find a match for sheet cell with database table cell by position
					foreach (DataGridViewRow r in dgMatches.Rows)
					{
						if (((ExcelCell)r.Cells[0].Value).ColumnName == columnPosition)
						{
							// if found, check for types and if the type is a string then add necessary symbols
							ExcelCell c = (ExcelCell)r.Cells[0].Value;

							if (((DBColumn)r.Cells[1].Value).CellType == "String")
								c.CellValue = "'" + cellValue + "'";
							else
							{
								// if column type isn't a string then just copy cell value
								c.CellValue = cellValue;
							}

							r.Cells[0].Value = (object)c;
							break;
						}
					}
				}

				// Set insert command text to begin of quary
				Cmd.CommandText = queryInsertColumns;

				// Add cell values to insert command test
				foreach (DataGridViewRow r in dgMatches.Rows)
				{
					ExcelCell xcell = (ExcelCell)r.Cells[0].Value;
					DBColumn dcell = (DBColumn)r.Cells[1].Value;

					if (dcell.CellType == "s")
					{
						// if sheet cell value is a string and its Length is greater than DB table cell length
						// then limit max. chars in cell value
						if (xcell.CellValue.Length > dcell.MaxLength)
							Cmd.CommandText += xcell.CellValue.Substring(xcell.CellValue.Length - dcell.MaxLength) + ",";
						else
							Cmd.CommandText += xcell.CellValue + ",";
					}
					else
						// if sheet cell is not a string that just copy its value to command text
						Cmd.CommandText += xcell.CellValue + ",";
				}

				Cmd.CommandText = Cmd.CommandText.Substring(0, Cmd.CommandText.Length - 1) + ")";
				//MessageBox.Show(Cmd.CommandText);
				RowNum++;
				try
				{
					// Try to execute insert command
					Cmd.ExecuteNonQuery();
					Invoke(delegateAddItemToListBox, new object[] { lbProgressLog, ThreadingHelper.FormatLogRow(String.Format("Log row {0} exported successfully", RowNum)) });
				}
				catch (Exception ex)
				{
					try
					{
						Invoke(delegateAddItemToListBox, new object[] { lbProgressLog, ThreadingHelper.FormatLogRow(String.Format("Error during exporting row {0} : {1}", RowNum, ex.Message)) });
					}
					catch
					{
						return false;
					}
				}

				// Move to the next row in the sheet
				xlsxRow = xlsxRow.NextSibling;


				Invoke(delegatePerformStep);
			}

			return true;
		}

		private bool AlterTable()
		{
			if (dbNewColumns == null) return true;
			if (dbNewColumns.Count <= 0) return true;
			if (lbocDBRow.Count <= 0) return true;
			if (TableName == "") return false;
			string AfterColumn = lbocDBRow[lbocDBRow.Count - 1].ToString();
			Cmd.CommandText = "ALTER TABLE [" + TableName + "] ADD ";

			bool notfirst = false;
			foreach (DBColumn dcell in dbNewColumns)
			{
				if (notfirst)
					Cmd.CommandText += ", ";
				else
					notfirst = true;
				string type;
				if (dcell.CellType == "String")
					type = "varchar(255)";
				else if (dcell.CellType == "Integer")
					type = "int";
				else if (dcell.CellType == "Float")
					type = "float";
				else if (dcell.CellType == "DateTime")
					type = "datetime";
				else
					type = "varchar(255)";
				Cmd.CommandText += "[" + dcell.ColumnName + "] " + type;//AFTER [" + AfterColumn + "]";
				AfterColumn = dcell.ColumnName;
			}

			try
			{
				Cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
				return false;
			}

			return true;
		}

		/***********************************/
		/* Show and close wizard functions */
		/***********************************/

		/// <summary>
		/// Event which occurs when wizard is shown
		/// </summary>
		private void wizardControl_Shown(object sender, EventArgs e)
		{
			wizardControl.Tag = (int)0;
			// Check for xlsx-file existence
			if (File.Exists(xlsxFileName))
			{
				// Try to open xlsx-file
				if (TryOpenLogFile(xlsxFileName))
				{
					// Load xlsx-file column names
					if (LoadExcelColumnNames())
					{
						// Get list of data providers
						dtProviders = DbProviderFactories.GetFactoryClasses();

						//TODO: [+] Removingcode: OracleClient data provider from provider list
						int i = 0;
						while (i < dtProviders.Rows.Count)
						{
							// Search OracleClient and SQL Server CE data provider
							if ((dtProviders.Rows[i].ItemArray[0].ToString().ToLower().Contains("oracle")) ||
									(dtProviders.Rows[i].ItemArray[0].ToString().ToLower().Contains("server ce")))
								// Removing OracleClient or SQL Server CE data provider
								dtProviders.Rows.RemoveAt(i);
							else
							{	// Add data provider's name to a listbox
								cbProvider.Items.Add(dtProviders.Rows[i].ItemArray[0]);
								++i;
							}
						}

						// Enable Next button
						btnNext.Enabled = false;

						//TODO: [+] Democode: pre-selecting SQLClient data provider (page 1)
						for (int j = 0; j < cbProvider.Items.Count; ++j)
						{
							if (cbProvider.Items[j].ToString().ToLower().Contains("sqlclient"))
							{
								cbProvider.SelectedIndex = j;
								break;
							}
						}

						return;
					}
					else
						MessageBox.Show("Cannot load header in " + xlsxFileName, "Xlsx-file error", MessageBoxButtons.OK, MessageBoxIcon.Error);
				}
				else
					MessageBox.Show("Cannot open file " + xlsxFileName,
							"Xlsx-file error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
			else
				MessageBox.Show("Cannot find file " + xlsxFileName,
						"Xlsx-file error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			wizardControl.Parent.Dispose();
		}

		/********************************************/
		/* Provider selection page wizard functions */
		/********************************************/

		/// <summary>
		/// Event whic occurs when selection of list of data providers is changed
		/// </summary>
		private void cbProvider_SelectedIndexChanged(object sender, EventArgs e)
		{
			if (cbProvider.SelectedIndex > -1)
			{
				// Set description label
				string providerName = cbProvider.SelectedItem.ToString().ToLower();
				if (providerName.Contains("odbc"))
					lblDesc.Text = "Use this selection to specify an ODBC user or system data source name to connect to an ODBC driver through the .NET Framework Data Provider for ODBC.";
				else if (providerName.Contains("ole db") || providerName.Contains("oledb"))
					lblDesc.Text = "Use this selection to connect to an OLE DB data sources through the .NET Framework Data Provider for OLE DB.";
				else if (providerName.Contains("sqlclient"))
					lblDesc.Text = "Use this selection to connect to Microsoft SQL Server using the .NET Framework Data Provider for SQL Server.";
				else
					lblDesc.Text = "";

				btnNext.Enabled = true;
			}
		}

		#region select table page

		/// <summary>
		/// Event which occurs when selection of table list is changed
		/// </summary>
		private void lbTableNames_SelectedIndexChanged(object sender, EventArgs e)
		{
			btnNext.Enabled = true;
			TableNameChanged = true;
		}

		/// <summary>
		/// Creates new table in database
		/// </summary>
		private void btnCreateNewTable_Click(object sender, EventArgs e)
		{
			// Check for name of new table
			if (tbNewTableName.Text == string.Empty)
			{
				MessageBox.Show("Enter a valid table name", "New table error", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}

			// Create new table and add it to the listbox
			if (CreateNewTable(tbNewTableName.Text))
				lbTableNames.SelectedIndex = lbTableNames.Items.Add(tbNewTableName.Text);
		}

		#endregion

		#region Column matching page

		/// <summary>
		/// Event which occurs when selection of center list has changed
		/// </summary>
		private void dgMatches_SelectionChanged(object sender, EventArgs e)
		{
			// Check for selection of center list
			if (dgMatches.SelectedRows.Count > 0)
				btnRemoveMatch.Enabled = true;
			else
				btnRemoveMatch.Enabled = false;

			if (dgMatches.Rows.Count > 0)
				btnNext.Enabled = true;
			else
				btnNext.Enabled = false;
		}

		private void lbLogColumns_SelectedIndexChanged(object sender, EventArgs e)
		{
			// Check for selection of center list and enable left add button
			if (lbDBColumns.SelectedIndex > -1 &&
					 lbLogColumns.SelectedIndex > -1)
				btnAddMatch.Enabled = true;
			else
				btnAddMatch.Enabled = false;

			if (lbLogColumns.SelectedIndex > -1)
				btnAddAsNewColumn.Enabled = true;
			else
				btnAddAsNewColumn.Enabled = false;
		}

		private void lbDBColumns_SelectedIndexChanged(object sender, EventArgs e)
		{
			// enable the match button if there is two selected items
			if (lbDBColumns.SelectedIndex > -1 &&
					 lbLogColumns.SelectedIndex > -1)
				btnAddMatch.Enabled = true;
			else
				btnAddMatch.Enabled = false;
		}

		/// <summary>
		/// Inserts a cell into left list considering its position in xlsx-file
		/// </summary>
		/// <param name="cell">Cell to insert</param>
		private void InsertInLogList(DataGridViewCell cell)
		{
			// Find position in xlsx-file
			int n = 0;
			for (int i = 0; i < lbocXlsxRow.Count; i++)
			{
				if (lbocXlsxRow[i].Name == cell.Value.ToString())
				{
					n = i;
					break;
				}
			}

			// Find position in the left list
			int k = 0;

			for (int j = 0; j < n; ++j)
			{
				if (k >= lbLogColumns.Items.Count)
					break;
				if (lbocXlsxRow[j].Name == lbLogColumns.Items[k].ToString())
					k++;
			}

			// Insert a cell
			lbLogColumns.Items.Insert(k, cell.Value);
		}

		/// <summary>
		/// Inserts a cell into right list considering its position in database table
		/// </summary>
		/// <param name="cell">Cell to insert</param>
		private void InsertInDBList(DataGridViewCell cell)
		{
			// Find position in database table
			int n = 0;
			for (int i = 0; i < lbocDBRow.Count; i++)
			{
				if (lbocDBRow[i].ColumnName == cell.Value.ToString())
				{
					n = i;
					break;
				}
			}

			// Find position in the right list
			int k = 0;

			for (int j = 0; j < n; ++j)
			{
				if (k >= lbDBColumns.Items.Count)
					break;
				if (lbocDBRow[j].ColumnName == lbDBColumns.Items[k].ToString())
					k++;
			}

			// Insert a cell
			lbDBColumns.Items.Insert(k, cell.Value);
		}

		/// <summary>
		/// Event which occurs when right Set button is pressed (move current selection of right list
		///																to the right side of center list)
		/// </summary>
		private void btnAddDBColumn_Click(object sender, EventArgs e)
		{
			// Copy to center list
			dgMatches.SelectedCells[1].Value = lbDBColumns.SelectedItem;
			lbDBColumns.Items.Remove(lbDBColumns.SelectedItem);	// Remove from right list

			// select the first item
			if (lbDBColumns.Items.Count > 0)
				lbDBColumns.SelectedIndex = 0;
		}

		/// <summary>
		/// Event which occurs when center Remove button is pressed
		/// </summary>
		private void btnRemoveBoth_Click(object sender, EventArgs e)
		{
			// put log column back in list
			InsertInLogList(dgMatches.SelectedCells[0]);
			lbLogColumns_SelectedIndexChanged(sender, null);

			// if the db column is not a new column, then add it back to the table list
			if (!dgMatches.SelectedCells[1].Value.ToString().Contains("(NEW)"))
			{
				InsertInDBList(dgMatches.SelectedCells[1]);
				lbDBColumns_SelectedIndexChanged(sender, null);
			}
			else
				dbNewColumns.Remove((DBColumn)dgMatches.SelectedCells[1].Value);


			// Remove row from the center list
			dgMatches.Rows.RemoveAt(dgMatches.SelectedRows[0].Index);
			dgMatches_SelectionChanged(null, null);

		}

		private void btnAddMatch_Click(object sender, EventArgs e)
		{
			dgMatches.Rows.Add(lbLogColumns.SelectedItem, lbDBColumns.SelectedItem);

			lbLogColumns.Items.Remove(lbLogColumns.SelectedItem);
			lbLogColumns_SelectedIndexChanged(null, null);

			lbDBColumns.Items.Remove(lbDBColumns.SelectedItem);
			lbDBColumns_SelectedIndexChanged(null, null);

			if (lbLogColumns.Items.Count > 0)
				lbLogColumns.SelectedIndex = 0;
			if (lbDBColumns.Items.Count > 0)
				lbDBColumns.SelectedIndex = 0;
		}

		private void btnAddAsNewColumn_Click(object sender, EventArgs e)
		{
			NewObjectForm nof = new NewObjectForm("Table Column", 30);
			nof.ObjectName = lbLogColumns.SelectedItem.ToString();

			if (nof.ShowDialog() == DialogResult.OK)
			{
				DBColumn dcell = new DBColumn(nof.ObjectName, nof.cbType.Text, 255, nof.ObjectName + " (NEW)");
				dgMatches.Rows.Add(lbLogColumns.SelectedItem, dcell);
				if (dbNewColumns == null)
					dbNewColumns = new List<DBColumn>();
				dbNewColumns.Add(dcell);
				lbLogColumns.Items.Remove(lbLogColumns.SelectedItem);
				lbLogColumns_SelectedIndexChanged(null, null);
			}
		}

		#endregion

		private void btnSaveLog_Click(object sender, EventArgs e)
		{
			if (saveFileDialog.ShowDialog() == DialogResult.OK)
				FileHelper.SaveListBoxToFile(lbProgressLog, saveFileDialog.FileName);
		}

		#region Threading functions

		// thread for exporting
		Thread thread = null;

		/// <summary>
		/// Thread-safe progress bar step 
		/// </summary>
		private void PerformStep()
		{
			if (!pbConverting.InvokeRequired)
				pbConverting.PerformStep();
			else
			{
				System.Delegate d = new ThreadingHelper.VoidDelegate(PerformStep);
				this.Invoke(d, null);
			}
		}

		/// <summary>
		/// Thread safe list box item adding
		/// </summary>
		/// <param name="listBox">List box which item is being added to</param>
		/// <param name="item">Item to add</param>
		private void AddItemToListBox(ListBox listBox, object item)
		{
			if (!listBox.InvokeRequired)
			{
				listBox.Items.Add(item);
				listBox.SelectedIndex = listBox.Items.Count - 1;
			}
			else
			{
				Delegate delegateAddItemToListBox = new ThreadingHelper.ListBoxDelegate(AddItemToListBox);
				Invoke(delegateAddItemToListBox);
			}
		}

		/// <summary>
		/// Finishes thread work
		/// </summary>
		private void FinalizeThread()
		{
			try
			{
				if (thread != null)
				{
					if (thread.ThreadState != ThreadState.Suspended)
					{
						thread.Abort();
						thread.Join();
						thread = null;
					}
				}
			}
			finally
			{
				lbProgressLog.Items.Add(ThreadingHelper.FormatLogRow("Finished"));
				btnNext.Enabled = false;
				btnCancel.Text = "Close";
			}
		}

		private void ThreadStart()
		{
			ConvertData();
			Delegate delegateFinalizeThread = new ThreadingHelper.VoidDelegate(FinalizeThread);

			try
			{
				this.Invoke(delegateFinalizeThread, null);
			}
			catch
			{
				thread = null;
			}
		}
		#endregion

		private void DemoWizardForm_FormClosed(object sender, FormClosedEventArgs e)
		{
			CloseLogFile();
			CloseDB();
		}
	}
}